SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE  PROCEDURE [Live].[DailyReporting]
	@FundID VARCHAR(50)
	, @NAVDate DATETIME = null
	, @ReportName VARCHAR(100) = null
	, @TRS VARCHAR(5) = null
AS
BEGIN

DECLARE @fmtonlyON BIT
SET @fmtonlyON = 0
IF (1=0) BEGIN SET @fmtonlyON = 1 END
SET FMTONLY OFF

Set nocount on ;
	DECLARE @PriorNAVDATE DATETIME
	DECLARE @Cash DECIMAL(30,2)
	DECLARE @netlevamt DECIMAL(30,2)
	DECLARE @fundlevyld DECIMAL(30,10)


	DECLARE @ExportID INT
	SET @ExportID = CASE WHEN @NAVDate IS NULL
							THEN (SELECT MAX(ex.ExportID) FROM FSFundModel.Reporting.Export ex WHERE FundID = @FundID)
						ELSE (SELECT MAX(ex.ExportID) FROM FSFundModel.Reporting.Export ex WHERE NAVDate = @NAVDate AND FundID = @FundID)
					END
	

	IF (@NAVDate IS NULL)
	BEGIN
		SELECT @NAVDate = e.NAVDate FROM FSFundModel.Reporting.Export e WHERE e.ExportID = @ExportID AND FundID = @FundID
	END
			 		 if 1=2 select null as column1
		
	SELECT 
		@PriorNAVDate = PrevNAVDate, @cash = CashAdj, @netlevamt = netlevamt,  @fundlevyld = Levyld
	FROM 
		FSFundModel.Reporting.fund f
	WHERE 
		f.exportid = @exportid 
		AND f.CurrNAVDate = @NAVDate
		AND f.FundID = @FundID

IF (@ReportName = 'NAVRec')
BEGIN
	SELECT 
		
		 CASE WHEN priorNAV.navshare = '0.0'
			THEN priorNAV.EstNAVShare
			ELSE priorNAV.navshare
			END AS NAVperSharePriorDay		
	FROM
		FSFundModel.Reporting.Export e
	INNER JOIN
		FSFundModel.Reporting.dailynav dn
	ON
		e.NAVDate = dn.NAVDate
		AND e.ExportID = dn.ExportID
		AND e.FundID = dn.FundID
	INNER JOIN
		FSFundModel.Reporting.DailyNAV priorNAV
	ON
		e.ExportID = priorNAV.ExportID
		AND priorNAV.NAVDate = @PriorNAVDate
		AND e.FundID = priorNAV.FundID 
	WHERE
		e.exportid = @exportid 
		AND e.FundID = @FundID
END
	


IF (@ReportName = 'FundData1')
BEGIN
	SELECT 
		CONVERT(VARCHAR, @PriorNAVDate, 101) AS Prior_NAV_Date
		, CONVERT(VARCHAR, @NAVDATE, 101) As Current_NAV_Date
		, @Cash AS AdjCash
		, @netlevamt AS net_leverage_amt
		, @fundlevyld AS fund_leverage_yield
	--select dailyvalchg, * from FSFundModel.Reporting.asset a inner join FSFundModel.Reporting.instrument i on a.exportid = i.exportid and a.instrumentid = i.instrumentid where a.exportid = @exportid and i.instrname like '%texas%'
	--and portfolioid like '%FSIC%' and i.instrumentid = '359'
END

IF (@ReportName = 'FundData2')
BEGIN
	SELECT 
		dn.NAVDate
		, CASE WHEN dn.navshare = '0.0'
			THEN dn.EstNAVShare
			ELSE dn.navshare
			END AS NAVperShareToday
		, CASE WHEN priorNAV.navshare = '0.0'
			THEN priorNAV.EstNAVShare
			ELSE priorNAV.navshare
			END AS NAVperSharePriorDay		
		, dn.DailyValChgActive
		, dn.DailyValChgExited
		, dn.DailyValChgUnfunded

	FROM
		FSFundModel.Reporting.Export e
	INNER JOIN
		FSFundModel.Reporting.dailynav dn
	ON
		e.NAVDate = dn.NAVDate
		AND e.ExportID = dn.ExportID
		AND e.FundID = dn.FundID
	INNER JOIN
		FSFundModel.Reporting.DailyNAV priorNAV
	ON
		e.ExportID = priorNAV.ExportID
		AND priorNAV.NAVDate = @PriorNAVDate
		AND e.FundID = priorNAV.FundID 
	WHERE
		e.exportid = @exportid 
		AND e.FundID = @FundID
END

IF (@ReportName = 'Leverage')
BEGIN
	SELECT 
		 p.PortfolioId,  p.PortfolioName, p.netlevamt , p.LevYld, p.LevYldMult, p.AllInLevCostPct
	FROM
		FSFundModel.Reporting.portfolio p
	WHERE
		p.exportid = @exportid 
		AND p.FundID = @FundID
END

IF (@ReportName = 'PortfolioDetails')
BEGIN
	SELECT 
		FundId 
		,PortfolioID 
		,TRSEndAdjAmt 
		,TRSTotAccrInc 
		,ExportId 
		,PortfolioIntrnlId 
		,PortfolioId 
		,FundId 
		,PortfolioName 
		,PortType 
		,ValuationType 
		,HasEscrowBal 
		,GrossPct 
		,NetPct 
		,IsQualPct 
		,MoodysRating 
		,PurchPrc 
		,LiqCash 
		,UnsettPurch 
		,UnsettSale 
		,UnsettMisc 
		,DrawnLev 
		,NetLevAmt 
		,PortAllocPct 
		,LevYld 
		,EBITDAAvg 
		,BegAdjAmt 
		,PIKAmt 
		,Paydown 
		,EndAdjAmt 
		,Cost 
		,AmortCost 
		,AmortCostPctPar 
		,MktVal 
		,UnrealDeprec 
		,UnrealApprec 
		,BegLoanCost 
		,AmortDisc 
		,EndAmortDiscPar 
		,UnamortDisc 
		,TotalDisc 
		,EndTotalDisc 
		,BegDiscBasis 
		,MthAccr 
		,EndDiscBasis 
		,QtrAmt 
		,UnrealGLPct 
		,UnrealAmortCost 
		,UnrealFairValue 
		,UnrealGLAmt 
		,RealGLPrinPmt 
		,RealGLAmortCost 
		,RealGLPct 
		,RealGLAmt 
		,CoupSetAnnual 
		,MthCoupAccrPctPar 
		,MthAmortPctPar 
		,CoupRev 
		,DiscRev 
		,TotalRev 
		,CoupCashFlow 
		,GAAPYldPurchPrc 
		,CurrYldPurchPrc 
		,PctCostDiscPurchPrc 
		,SettDiscPurchPrc 
		,TotalIntPurchPrc 
		,GAAPYldCurrPrc 
		,CurrYldCurrPrc 
		,PctCostDiscCurrPrc 
		,SettDiscCurrPrc 
		,CoupRevAnnualPurch 
		,TotalIntCurrPurchPrc 
		,InvWalkGAAPYld 
		,EstLevYld 
		,UnfundRevolv 
		,CashOnHand 
		,EstComm 
		,EstCommPct 
		,EstOOReimb 
		,AvailInvPreRes 
		,IntExp 
		,EstExp 
		,ContMargin 
		,VarMargin 
		,TotMargin 
		,CashReserve 
		,AvailInv 
		,AvailLev 
		,EstNetAssVal 
		,EstLev 
		,EscrowCash 
		,NetEscrow 
		,DrawnLevIncTRS 
		,AvailInvPost 
		,AvailLevAmt 
		,AvailLevPct 
		,LevSpreadPct 
		,Capacity 
		,ReqMargin 
		,AnnualAmortExpPct 
		,OrigAmortCost 
		,RemBalOrigAmort 
		,RemBalOrigAmortDate 
		,RemBalMthAmort 
		,FacilityMatDate 
		,LevSpreadExpPct 
		,LevSpreadMgtExpPct 
		,AllInLevCostPct 
		,Score 
		,FaceLevThruTrn 
		,IntCov 
		,TRSIncCommFees 
		,TRSAccrIncUnrec 
		,TRSGL 
		,TRSCompCarry 
		,TRSFees 
		,TRSInt 
		,TRSTotAccrInc 
		,DailyValChg 
		,DailyPrcChg 
		,CashOnHandCov 
		,MktValBud 
		,TRSEndAdjAmt 
		,TRSAmortCost 
		,TRSLevAmt 
		,TRSTotalIntPurchPrc 
		,TRSSettDiscPurchPrc 
		,TRSPIKAmt 
		,TRSCoupRevAnnual 
		,TRSMktVal 
		,TRSPortAllocPct 
		,TRSLevYld 
		,TRSLevFactor 
		,TRSGAAPYldCurrPrc 
		,TRSMarginThresh 
		,UnrealApprecTRS 
		,UnrealDeprecTRS 
		,SortOrder 
		,CreatedDate 
		,CreatedBy 
		,ModifiedDate 
		,ModifiedBy 
		,AddlAmort 
		,AddlCapExp 
		,PortLeverageType 
		,AvgMaturity 
		,EstDuration 
		,AvgMaturityDate 
		,CashInAmt 
		,LevYldMult 
		,LevYldProp 
		,MktValProp 
		,NearTermPaydowns 
		,NetAvailLevAmt 
		,NetCashPostLev 
		,NetCashPreLev 
		,NetUnsettled 
		,Outflows 
		,PositionAmt 
		,PrincipalForMktVal 
		,TodayPrice 
		,YesterdayPrice 
		,CompCnt 
		,RealGainLossTot 
		,RealGainLossToday 
		,DailyValChgAdjAmt 
		,DailyValChgExitAmt 
		,CurrentCash 
		,NetCashPostLevTRS 
		,TRSInvWalkGAAPYld
	FROM 
		FSFundModel.Reporting.Portfolio
	WHERE 
		fundID = @FundID
		and ExportId = @ExportID
END

IF (@ReportName = 'Rates')
BEGIN
	select 
		BaseRateType, RatePct
	From 
		FSFundModel.Reporting.rate
	Where
		exportid = @exportid 
END

	DECLARE @results TABLE
		(
			PortfolioID VARCHAR(50)
			, InstrClass VARCHAR(100)
			, InstrName VARCHAR(200)
			, OrigPurchDate DATETIME
			, IsQualified VARCHAR(2)
			, AssetQualityRating INT
			, PricingDepth VARCHAR(10)
			, EstimateDuration DECIMAL(38,12)
			, SettleDate_WeightedAvg DATETIME
			, MaturityDate DATETIME
			, Moody_Rating VARCHAR(50)
			, GAAP_Pur VARCHAR(50)
			, GAAP_Cur VARCHAR(50)
			, GAAP_Walk VARCHAR(50)
			, Sum_Par DECIMAL(38,12)
			, NotionalVal DECIMAL(38,12)
			, Amort_Cost_Pct_Par VARCHAR(50)
			, Weight_Price VARCHAR(50)
			, PriorNAV_Price DECIMAL(38,12)
			, NAVDate_Price DECIMAL(38,12)
			, Spread DECIMAL(38,12)
			, [Floor] DECIMAL(38,12)
			, FixedRate DECIMAL(38,12)
			, Amort_Cost DECIMAL(38,12)
			, MktVal DECIMAL(38,12)
			, MktVal_Pct DECIMAL(38,12)
			, Net_Unreal_App DECIMAL(38,12)
			, Pct_Net_Unreal_Gain VARCHAR(50)
			, DailyVal_Change DECIMAL(38,12)
			, PriceChange_Pct VARCHAR(50)
			, SecurityID VARCHAR(50)
			, Sum_Unreal_Dep DECIMAL(38,12)
			, Sum_Unreal_App DECIMAL(38,12)
			, Prop_Reason VARCHAR(50)
			, PricingMethod VARCHAR(50)
			, TotalInt_Purch_Price DECIMAL(38,12)
			, TotalInt_Cur_Price DECIMAL(38,12)
			, Sum_OrigCost DECIMAL(38,12)
			, Security_Type varchar(50)
			, Issuer_Name varchar(50)
			, Lots DECIMAL(38,12)
			, Iss_SubSector varchar(100)
			, Iss_Sector varchar(100)
			, GSO_Industry varchar(100)
			, ExternalID varchar(20)
			--, FocusGroup varchar(5)
			, TTMEBITDA VARCHAR(20)
			, MostRecentData_EBITDA VARCHAR(10)
			, FaceLevelThruTrn VARCHAR(20)
			, IntCov VARCHAR(20)
			, Inst_SortOrder INT
			, Port_SortOrder INT
			, BaseRateType VARCHAR(50)
		)

	INSERT INTO @results
		(
			PortfolioID 
			, InstrClass 
			, InstrName 
			, OrigPurchDate
			, IsQualified 
			, AssetQualityRating 
			, EstimateDuration 
			, SettleDate_WeightedAvg
			, MaturityDate
			, Moody_Rating 
			, GAAP_Pur 
			, GAAP_Cur 
			, GAAP_Walk 
			, Sum_Par 
			, NotionalVal
			, Amort_Cost_Pct_Par 
			, Weight_Price 
			, PriorNAV_Price 
			, NAVDate_Price 
			, Amort_Cost 
			, MktVal 
			, Net_Unreal_App 
			, Pct_Net_Unreal_Gain 
			, DailyVal_Change 
			, PriceChange_Pct 
			, SecurityID 
			, Sum_Unreal_Dep 
			, Sum_Unreal_App 
			, Prop_Reason
			, PricingMethod 
			, TotalInt_Purch_Price 
			, TotalInt_Cur_Price 
			, Sum_OrigCost 
			, Security_Type
			, Issuer_Name
			, Lots
			, Iss_SubSector
			, Iss_Sector
			, GSO_Industry
			, ExternalID
			, TTMEBITDA
			, MostRecentData_EBITDA
			, FaceLevelThruTrn
			, IntCov
			, Spread
			, [Floor]
			, FixedRate
			, Inst_SortOrder
			, BaseRateType
		)
	SELECT
		a.PortfolioID
		, id.InstrClass
		, i.InstrName
		, MIN(a.PurchDate)
		, CASE WHEN MAX(Cast(fi.IsQualified AS INT)) = '1'
			THEN 'Y'
			ELSE 'N'
			END 
		, MAX(i.ASSQualrat)
		, MAX(a.ESTDuration)
		, CASE WHEN SUM(a.EndAdjAmt) <> '0.0'
				THEN CONVERT(DATETIME, SUM(CAST(a.SettlementDate AS DECIMAL) * a.EndAdjAmt) / SUM(a.EndAdjAmt), 101)
				ELSE MAX(a.SettlementDate)
			END
		, MAX(i.MaturityDate)
		, MAX(i.MoodysRating)
		, CASE WHEN MAX(i.InstDefId) < 9 OR MAX(i.InstDefId) >11 THEN CAST(MAX(a.GAAPYldPurchPrc)  AS VARCHAR)
		 ELSE	'N/A'
		 END	
			, CASE WHEN MAX(i.InstDefId) < 9 OR MAX(i.InstDefId) >11 THEN	 CAST(MAX(a.GAAPYldCurrPrc)  AS VARCHAR)
		 ELSE	'N/A'
		 END	 
		 , CASE WHEN MAX(i.InstDefId) < 9 OR MAX(i.InstDefId) >11 THEN	 CAST(MAX(a.InvWalkGAAPYld)  AS VARCHAR)
		 ELSE	'N/A'
		 END	 
		, SUM(a.EndAdjAmt) 
		, SUM(i.NotionalVal)
		,CASE WHEN SUM(a.AmortCost)<> '0.0'
			THEN  CAST(SUM(a.AmortCost)/ SUM(a.EndAdjAmt) AS VARCHAR)
			ELSE 'N/A'
			END
		, CASE WHEN SUM(a.EndAdjAmt) <> '0.0'
			THEN CAST(SUM(a.PurchPrc * a.EndAdjAmt) / SUM(a.EndAdjAmt) AS VARCHAR)
			ELSE 'N/A'
			END 
		, Max(a.YesterdayPrice) 
		, MAX(a.TodayPrice) 
		, SUM(a.AmortCost) 
		, SUM(a.MktVal) 
		, SUM(a.UnrealDeprec) + sum(a.unrealapprec) 
		, CASE WHEN SUM(a.AmortCost)<> '0.0'
			THEN CAST((SUM(a.UnrealDeprec) + sum(a.unrealapprec)) / SUM(a.AmortCost) AS VARCHAR)
			ELSE 'N/A'
			END
		, SUM(a.DailyValChg) 
		, CASE WHEN  Max(a.YesterdayPrice) > '0.01' and MAX(a.TodayPrice) > '0.01' 
			THEN CAST((( MAX(a.TodayPrice) / Max(a.YesterdayPrice) ) -1) AS VARCHAR)
			ELSE 'N/A'
			END 
		
		, MAX(i.extid) 
		, SUM(a.UnrealDeprec)
		, SUM(a.UnrealApprec) 
		/*, CASE WHEN MAX(CAST(a.IsProprietary AS INT)) = 1
				THEN 'Y'
				ELSE 'N'
			END as Asset_Proprietary*/
		, MAX(ISNULL(ah.NBSReason, ''))
		, CASE WHEN MAX(i.PricingMethodType) = 'Cost'
				THEN 'Cost - to be ' + MAX(i.PricingVendor)
				ELSE MAX(i.PricingVendor)
			END
		, SUM(a.totalIntPurchPrc)
		, SUM(a.totalintcurrpurchprc) 
		, SUM(a.Cost) 
		, max(id.InstTypeDescr)
		, max(iss.IssuerName)
		, count(a.Instrumentid)
		, max(iss.SubSector)
		, max(iss.GICSSector)
		, MAX(iss.GSOInd)
		, max(i.ExtId)
		, CASE WHEN max(iss.TTMEBITDA) = '0.0' THEN 'NA' ELSE CAST(max(iss.TTMEBITDA) AS VARCHAR(20)) END
		, max(iss.MostRecentData)
		, CASE WHEN max(i.FaceLevThruTrn) = '0.0' THEN 'NA' ELSE CAST(max(i.FaceLevThruTrn) AS VARCHAR(20)) END
		, CASE WHEN max(iss.IntCov) = '0.0' THEN 'NA' ELSE CAST(max(iss.IntCov) AS VARCHAR(20)) END
		, MAX(i.Spread)
		, MAX(i.[Floor])
		, MAX(i.FixedRate)
		, MAX(id.SortOrder)
		, MAX(i.BaseRateType)
	FROM
		FSFundModel.Reporting.Asset a
	INNER JOIN
		FSFundModel.Reporting.Instrument i
	ON
		a.ExportID = i.ExportID
		AND a.InstrumentID = i.InstrumentID
	INNER JOIN
		FSFundModel.Reporting.Issuer iss
	ON
		a.ExportID = iss.ExportID
		AND i.IssuerID = iss.IssuerID
	INNER JOIN
		FSFundModel.Reporting.InstrumentDefinition id
	ON
		a.ExportID = id.ExportID
		AND i.InstDefID = id.InstDefID
	INNER JOIN
		FSFundModel.Reporting.FundIssuer fi
	ON
		a.ExportID = fi.ExportID
		AND i.IssuerID = fi.IssuerID
	INNER JOIN reportingscripts.live.AssetHelper (@exportid) AH on ah.AssetIntrnlId = a.AssetIntrnlId

		WHERE
		a.ExportID = @ExportID
		AND a.IsActiveEndOfDay = 1
				
	GROUP BY
		a.PortfolioID
		, id.InstrClass
		, i.InstrName
	--having i.instrName like '%texas%'
	



	DECLARE @totalMktVal DECIMAL(38,12)
	SET @totalMktVal = (SELECT SUM(MktVal) FROM @results)

	UPDATE
		r
	SET
		r.MktVal_Pct = r.MktVal / @totalMktVal
	FROM
		@results r
	
	UPDATE
		r
	SET
		r.Port_SortOrder = p.SortOrder
	FROM
		@results r
	LEFT JOIN
		[FSFundModel].[Reporting].Portfolio p
	ON
		r.PortfolioID = p.PortfolioID
	
	DELETE FROM
		r
	FROM
		@results r
	INNER JOIN
		FSFundModel.Reporting.Portfolio p
	ON
		r.PortfolioID = p.PortfolioID
		AND p.ExportID = @ExportID
	WHERE
		p.PortType = CASE WHEN ISNULL(@TRS, '') = 'No' THEN 'TRS' ELSE NULL END
		

	---------------------------
	--Pricing
	---------------------------

IF (@ReportName = 'Pricing')
BEGIN
	--SELECT
	--	r.InstrName 
	--	, r.PricingMethod 	
	--	, r.SecurityID 
	--	, max(r.NAVDate_Price) AS CurrentDayPrice
	--	, max(r.Security_Type) AS SecurityType
	--	, r.Iss_SubSector
	--	, r.Iss_Sector
		

	--FROM
	--	@results r
		
	--	GROUP BY
	--	r.PricingMethod
	--	, r.InstrName
	--	, r.SecurityID 
	--	, r.Iss_SubSector
	--	, r.Iss_Sector

	
			;WITH prc AS(
				SELECT

					[InstrName]
				, CASE WHEN [i].[PricingMethodType] = 'Cost' THEN [i].[PricingMethodType] + ' - To Be ' + [i].[PricingVendor] ELSE [i].[PricingVendor] END AS 'Pricing Method'
				, [i].[ExtId] AS 'SecurityId'
				, CASE WHEN [i].[CurrConv] <> 'NA' AND [id].FinType = 'Equity' THEN ROUND([a].[TodayPrice] / CAST([er].[ExhangeRatePct] AS DECIMAL (38,4)), 4) ELSE 
				
						CASE WHEN  [i].[PricingMethodType] = 'Cost' THEN [a].[PurchPrc] ELSE 
						[a].[TodayPrice] END 
				
				END AS CurrentDayPrice
				, [id].[InstrClass]  AS secType
				, [iss].[SubSector] AS SubSector
				, [iss].[GICSSector] AS Sector


				FROM
					[FSFundModel].[Reporting].[Asset] a
					INNER JOIN [FSFundModel].[Reporting].[Instrument] i ON [i].[InstrumentId] = [a].[InstrumentId] AND [i].[ExportId] = [a].[ExportId]

					INNER JOIN [FSFundModel].[Reporting].[Fund] f ON [f].[ExportId] = [a].[ExportId]
				INNER JOIN  [FSFundModel].[Reporting].[Issuer] iss ON [iss].[ExportId] = [a].[ExportId] AND [iss].[IssuerId] = [i].[IssuerId]
				INNER JOIN  [FSFundModel].[Reporting].[InstrumentDefinition] id ON [id].[ExportId] = [i].[ExportId] AND [id].[InstDefId] = [i].[InstDefId]

					LEFT JOIN  [FSFundModel].[Reporting].[ExchangeRate] er ON [er].[ExportId] = [i].[ExportId] AND [er].[CurrConv] = [i].[CurrConv]
					AND [er].[ExchRateDate] = [f].[CurrNavDate]
	
				WHERE
				--	[InstrName] LIKE 'Swift
						[a].[IsActiveEndOfDay] =1

					AND [a].[ExportId] = @ExportId
					)

		--			SELECT * FROM  prc
		
					SELECT 	

	
						[InstrName]
					, [Pricing Method]
					, [SecurityId]
					, MAX([CurrentDayPrice]) CurrentDayPrice
					, MAX([secType]) SecurityType
					, MAX([SubSector]) SubSector
					, MAX([Sector])  Sector
	
					FROM  [prc]
					GROUP BY [Pricing Method],[InstrName],[SecurityId]
END 

	------------------------------------
	-- Full view
	------------------------------------

IF (@ReportName = 'DetailedView')
BEGIN
	SELECT
		r.PortfolioID 
		, r.InstrClass
		, r.InstrName
		, r.SecurityID AS Security_Identifier
		, r.IsQualified as Is_Qualified
		, r.AssetQualityRating as Asset_Quality_Rating
		, r.EstimateDuration AS Estimated_Duration
		, r.Moody_Rating as Moody_Rating
		, r.GAAP_Pur AS GAAP_Yield_At_Purchase_Price
		, r.GAAP_Cur AS GAAP_Yield_At_Current_Price
		, r.GAAP_Walk AS Walk_In_GAAP_Yield
		, CASE WHEN InstrClass = 'Equity/Other' and (r.GAAP_Pur = 'N/A')--r.GAAP_Pur = '0.000000'
					THEN ''
				WHEN InstrClass = 'Equity/Other' and CAST(r.GAAP_Pur AS DECIMAL(38,12)) = '0.0'
					THEN ''
				ELSE CAST(r.Sum_Par AS VARCHAR)
			END AS Sum_Of_Adj_Par
		, r.Amort_Cost_Pct_Par AS Amortized_Cost_Pct_of_Par
		, r.Weight_Price AS Weighted_Avg_Price
		, r.PriorNAV_Price AS PriorNAVDate_Price  -- fix with dynamic SQL later
		, r.NAVDate_Price AS NAVDate_Price  -- fix with dynamic SQL later
		, r.Amort_Cost AS Sum_Of_Amort_Cost
		, r.MktVal AS Sum_Of_Mkt_Val
		, r.MktVal_Pct AS Percent_of_MarketValue
		, r.Net_Unreal_App AS Net_Unrealized_Appreciation
		, r.Pct_Net_Unreal_Gain AS Pct_Net_Unreal_Gain
		, r.DailyVal_Change AS Sum_Of_Daily_Value_Change
		, r.PriceChange_Pct AS Price_Chg_Pct 
		, r.Sum_Unreal_Dep AS Sum_Of_Unreal_Dep
		, r.Sum_Unreal_App AS Sum_Of_Unreal_App
		, r.Prop_Reason AS Proprietary_Reason
		, r.PricingMethod AS Pricing_Method_Type
		, r.TotalInt_Purch_Price AS Interest_purchase_price_method
		, r.TotalInt_Cur_Price AS Interest_current_price_method
		, r.Sum_OrigCost AS Sum_Of_Original_Cost
		, r.Security_Type
		, r.MostRecentData_EBITDA
		, r.TTMEBITDA
		, r.IntCov
		, r.FaceLevelThruTrn
	FROM
		@results r
END


	------------------------------------
	-- Reduced column view
	------------------------------------
IF (@ReportName = 'PortfolioHoldings')
BEGIN
	SELECT
		r.PortfolioID 
		, r.InstrClass
		, CASE WHEN r.InstrClass = 'Equity/Other' 
					THEN  r.Issuer_Name + ' - ' + r.Security_Type
				ELSE r.InstrName
			END AS Instrument_Name
		, r.IsQualified as Is_Qualified
		, r.AssetQualityRating as Asset_Quality_Rating
		, r.EstimateDuration AS Estimated_Duration
		, r.Moody_Rating as Moody_Rating
		, r.GAAP_Pur AS GAAP_Yield_At_Purchase_Price
		, r.GAAP_Cur AS GAAP_Yield_At_Current_Price
		, r.GAAP_Walk AS Walk_In_GAAP_Yield
		, CASE WHEN InstrClass = 'Equity/Other' and (r.GAAP_Pur = 'N/A')--r.GAAP_Pur = '0.000000'
					THEN ''
				WHEN InstrClass = 'Equity/Other' and CAST(r.GAAP_Pur AS DECIMAL(38,12)) = '0.0'
					THEN ''
				ELSE CAST(r.Sum_Par / '1000.0'  AS VARCHAR) 
			END AS Sum_Of_Adj_Par
		, r.Amort_Cost_Pct_Par AS Amortized_Cost_Pct_of_Par
		, r.Weight_Price AS Weighted_Avg_Price
		, r.PriorNAV_Price AS PriorNAVDate_Price  -- fix with dynamic SQL later
		, r.NAVDate_Price AS NAVDate_Price  -- fix with dynamic SQL later
		, r.Amort_Cost / '1000.0' AS Sum_Of_Amort_Cost
		, r.MktVal / '1000.0' AS Sum_Of_Mkt_Val
		, r.DailyVal_Change / '1000.0' AS Sum_Of_Daily_Value_Change
		, r.Pct_Net_Unreal_Gain AS Pct_Net_Unreal_Gain
		, r.PricingMethod AS Pricing_Method_Type
		, r.Lots
		, r.Iss_SubSector
		, r.ExternalID
			
	FROM
		@results r
END
		

	------------------------------------
	-- Instrument Type View
	------------------------------------
IF (@ReportName = 'InstrumentClass')
BEGIN
	SELECT
		r.PortfolioID 
		, r.InstrClass
		,''--, r.InstrName
		, SUM(CASE WHEN r.IsQualified = 'Y' THEN r.MktVal ELSE '0.0' END) / SUM(MktVal) AS Is_Qualified_Pct
		,''--, r.AssetQualityRating as Asset_Quality_Rating
		, SUM(r.EstimateDuration * r.MktVal) / SUM(r.MktVal) AS Estimated_Duration_WeightAvg
		,''--, r.Moody_Rating as Moody_Rating
		, CASE WHEN SUM(r.Sum_OrigCost) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Purch_Price) as DECIMAL(38,12)) / CAST(SUM(r.Sum_OrigCost) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS GAAP_Yield_At_Purchase_Price
		, CASE WHEN SUM(r.MktVal) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Cur_Price) as DECIMAL(38,12)) / CAST(SUM(r.MktVal) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS GAAP_Yield_At_Current_Price
		, CASE WHEN SUM(r.MktVal) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Purch_Price) as DECIMAL(38,12)) / CAST(SUM(r.MktVal) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS Walk_In_GAAP_Yield
		, SUM(
				CASE WHEN InstrClass = 'Equity/Other' and (r.GAAP_Pur = 'N/A')--r.GAAP_Pur = '0.000000'
						THEN '0.0'
					WHEN InstrClass = 'Equity/Other' and CAST(r.GAAP_Pur AS DECIMAL(38,12)) = '0.0'
						THEN '0.0'
					WHEN r.GAAP_Pur = 'N/A'
						THEN '0.0'
					ELSE r.Sum_Par
				END 
			) / '1000.0' AS Sum_Of_Adj_Par
		
		, SUM(r.Amort_Cost) / SUM(r.Sum_Par) AS Amortized_Cost_Pct_of_Par
		, SUM(CASE WHEN r.Weight_Price = 'N/A' THEN '0.0' ELSE CAST(r.Weight_Price AS DECIMAL(38,12)) * r.Sum_Par END) / SUM(r.Sum_Par) AS Weighted_Avg_Price
		, SUM(r.PriorNAV_Price * r.Sum_Par) / SUM(r.Sum_Par) AS PriorNAVDate_Price
		, SUM(r.NAVDate_Price * r.Sum_Par) / SUM(r.Sum_Par) AS NAVDate_Price
		, SUM(r.Amort_Cost) / '1000.0' AS Sum_Of_Amort_Cost
		, SUM(r.MktVal) / '1000.0' AS Sum_Of_Mkt_Val
		, SUM(r.DailyVal_Change) / '1000.0' AS Sum_Of_Daily_Value_Change
		, SUM(r.MktVal) / SUM(r.Amort_Cost) - '1.0' AS Pct_Gain
		--add netlevamt
		
	FROM
		@results r
	GROUP BY
		r.PortfolioID 
		, r.InstrClass
END
		
	------------------------------------
	-- Portfolio View
	------------------------------------
IF (@ReportName = 'PortfolioTotals')
BEGIN
	SELECT
		r.PortfolioID 
		, ''--, r.InstrClass
		,''--, r.InstrName
		, SUM(CASE WHEN r.IsQualified = 'Y' THEN r.MktVal ELSE '0.0' END) / SUM(MktVal) AS Is_Qualified_Pct
		,''--, r.AssetQualityRating as Asset_Quality_Rating
		, SUM(r.EstimateDuration * r.MktVal) / SUM(r.MktVal) AS Estimated_Duration_WeightAvg
		,''--, r.Moody_Rating as Moody_Rating
		, CASE WHEN SUM(r.Sum_OrigCost) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Purch_Price) as DECIMAL(38,12)) / CAST(SUM(r.Sum_OrigCost) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS GAAP_Yield_At_Purchase_Price
		, CASE WHEN SUM(r.MktVal) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Cur_Price) as DECIMAL(38,12)) / CAST(SUM(r.MktVal) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS GAAP_Yield_At_Current_Price
		, CASE WHEN SUM(r.MktVal) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Purch_Price) as DECIMAL(38,12)) / CAST(SUM(r.MktVal) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS Walk_In_GAAP_Yield
		, SUM(
				CASE WHEN InstrClass = 'Equity/Other' and (r.GAAP_Pur = 'N/A')--r.GAAP_Pur = '0.000000'
						THEN '0.0'
					WHEN InstrClass = 'Equity/Other' and CAST(r.GAAP_Pur AS DECIMAL(38,12)) = '0.0'
						THEN '0.0'
					WHEN r.GAAP_Pur = 'N/A'
						THEN '0.0'
					ELSE r.Sum_Par
				END 
			) / '1000.0' AS Sum_Of_Adj_Par
		
		, SUM(r.Amort_Cost) / SUM(r.Sum_Par) AS Amortized_Cost_Pct_of_Par
		, SUM(CASE WHEN r.Weight_Price = 'N/A' THEN '0.0' ELSE CAST(r.Weight_Price AS DECIMAL(38,12)) * r.Sum_Par END) / SUM(r.Sum_Par) AS Weighted_Avg_Price
		, SUM(r.PriorNAV_Price * r.Sum_Par) / SUM(r.Sum_Par) AS PriorNAVDate_Price
		, SUM(r.NAVDate_Price * r.Sum_Par) / SUM(r.Sum_Par) AS NAVDate_Price
		, SUM(r.Amort_Cost) / '1000.0' AS Sum_Of_Amort_Cost
		, SUM(r.MktVal) / '1000.0' AS Sum_Of_Mkt_Val
		, SUM(r.DailyVal_Change) / '1000.0' AS Sum_Of_Daily_Value_Change
		, SUM(r.MktVal) / SUM(r.Amort_Cost) - '1.0' AS Pct_Gain	
		--, p.NetLevAmt AS NetLeverageAmt
		
	FROM
		@results r
	GROUP BY
		r.PortfolioID 
END

	------------------------------------
	-- Total Line
	------------------------------------
IF (@ReportName = 'TotalLine')
BEGIN
	SELECT
		''--portfolio
		,''--, r.InstrClass
		,''--, r.InstrName
		, SUM(CASE WHEN r.IsQualified = 'Y' THEN r.MktVal ELSE '0.0' END) / SUM(MktVal) AS Is_Qualified_Pct
		,''--, r.AssetQualityRating as Asset_Quality_Rating
		, SUM(r.EstimateDuration * r.MktVal) / SUM(r.MktVal) AS Estimated_Duration_WeightAvg
		,''--, r.Moody_Rating as Moody_Rating
		, CASE WHEN SUM(r.Sum_OrigCost) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Purch_Price) as DECIMAL(38,12)) / CAST(SUM(r.Sum_OrigCost) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS GAAP_Yield_At_Purchase_Price
		, CASE WHEN SUM(r.MktVal) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Cur_Price) as DECIMAL(38,12)) / CAST(SUM(r.MktVal) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS GAAP_Yield_At_Current_Price
		, CASE WHEN SUM(r.MktVal) <> '0.0'
				THEN CAST(CAST(SUM(r.TotalInt_Purch_Price) as DECIMAL(38,12)) / CAST(SUM(r.MktVal) as DECIMAL(38,12)) as VARCHAR) 
				ELSE 'N/A'
			END AS Walk_In_GAAP_Yield
		, SUM(
				CASE WHEN InstrClass = 'Equity/Other' and (r.GAAP_Pur = 'N/A')--r.GAAP_Pur = '0.000000'
						THEN '0.0'
					WHEN InstrClass = 'Equity/Other' and CAST(r.GAAP_Pur AS DECIMAL(38,12)) = '0.0'
						THEN '0.0'
					WHEN r.GAAP_Pur = 'N/A'
						THEN '0.0'
					ELSE r.Sum_Par
				END 
			) / '1000.0' AS Sum_Of_Adj_Par
		
		, SUM(r.Amort_Cost) / SUM(r.Sum_Par) AS Amortized_Cost_Pct_of_Par
		, SUM(CASE WHEN r.Weight_Price = 'N/A' THEN '0.0' ELSE CAST(r.Weight_Price AS DECIMAL(38,12)) * r.Sum_Par END) / SUM(r.Sum_Par) AS Weighted_Avg_Price
		, SUM(r.PriorNAV_Price * r.Sum_Par) / SUM(r.Sum_Par) AS PriorNAVDate_Price
		, SUM(r.NAVDate_Price * r.Sum_Par) / SUM(r.Sum_Par) AS NAVDate_Price
		, SUM(r.Amort_Cost) / '1000.0' AS Sum_Of_Amort_Cost
		, SUM(r.MktVal) / '1000.0' AS Sum_Of_Mkt_Val
		, SUM(r.DailyVal_Change) / '1000.0' AS Sum_Of_Daily_Value_Change
		, SUM(r.MktVal) / SUM(r.Amort_Cost) - '1.0' AS Pct_Gain
		
	FROM
		@results r
END
	
	
	------------------------------------
	-- Valuation Committee Summary
	------------------------------------

IF (@ReportName like 'ValComm_Summary%')

BEGIN

	/*SELECT 
		@NAVDate
		, CASE WHEN MONTH(@NAVDate) = 3 THEN '12'
							ELSE (MONTH(@NAVDate) - 3)
						END
		, ( SELECT MAX(NAVDate) FROM FSFundModel.Reporting.Export WHERE MONTH(NAVDate) = CASE WHEN MONTH(@NAVDate) = 3 THEN '12' ELSE (MONTH(@NAVDate) - 3) END )
		
		exec [Reporting].[DailyReporting] 'FSIC', '3/22/2013', 'ValComm_Summary'


		exec [Reporting].[DailyReporting] 'FSIC', '6/22/2013', 'ValComm_Summary'


		exec [Reporting].[DailyReporting] 'FSIC', '9/22/2013', 'ValComm_Summary'


		exec [Reporting].[DailyReporting] 'FSIC', '12/22/2013', 'ValComm_Summary'
		*/

	----
	-- Get pricing depth
	----
	
	UPDATE
		r
	SET
		r.PricingDepth = CASE WHEN markit.Depth IS NULL 
									THEN '1' 
								WHEN markit.Depth = 'implied'
									THEN '1'
								ELSE markit.Depth 
							END
		, r.Spread = p.Offer - p.Bid
	FROM
		@results r
	INNER JOIN
		FSFundModel.Reporting.Instrument i
	ON
		r.InstrName = i.InstrName
		AND i.ExportID = @ExportID
	LEFT JOIN
		FSPricing.dbo.Pricing_MarkitLoans_Marks markit
	ON
		i.ExtID = markit.LoanXID
		AND markit.MarkDate = @NAVDate
	LEFT JOIN
		FSFundModel.Reporting.Pricing p
	ON
		i.ExtID = p.PricingID
		AND i.ExportID = p.ExportID
		
	
	--UPDATE
	--	r
	--SET
	--	r.FocusGroup = CASE WHEN r.AssetQualityRating in ('4', '5')
	--							THEN 'Yes'
	--						WHEN r.PricingMethod LIKE '%VRC%'
	--							THEN 'Yes'
	--						WHEN r.PricingMethod LIKE '%Cost%'
	--							THEN 'Yes'
	--						ELSE 'No'
	--					END
	--FROM
		--@results r
		

	SELECT
		r.PortfolioID 
		, r.InstrClass
		, r.InstrName
		, r.OrigPurchDate
		, r.SecurityID AS Security_Identifier
		, r.IsQualified as Is_Qualified
		, r.AssetQualityRating as Asset_Quality_Rating
		, r.PricingDepth AS Depth
		, r.EstimateDuration AS Estimated_Duration
		, r.Moody_Rating as Moody_Rating
		, CASE WHEN r.Spread IS NULL
					THEN 'N/A'
				ELSE CAST(r.Spread AS VARCHAR)
			END AS Spread
		, r.GAAP_Pur AS GAAP_Yield_At_Purchase_Price
		, r.GAAP_Cur AS GAAP_Yield_At_Current_Price
		, r.GAAP_Walk AS Walk_In_GAAP_Yield
		, CASE WHEN InstrClass = 'Equity/Other' and (r.GAAP_Pur = 'N/A')--r.GAAP_Pur = '0.000000'
					THEN ''
				WHEN InstrClass = 'Equity/Other' and CAST(r.GAAP_Pur AS DECIMAL(38,12)) = '0.0'
					THEN ''
				ELSE CAST(r.Sum_Par AS VARCHAR)
			END AS Sum_Of_Adj_Par
		, r.Amort_Cost_Pct_Par AS Amortized_Cost_Pct_of_Par
		, r.Weight_Price AS Weighted_Avg_Price
		, r.PriorNAV_Price AS PriorNAVDate_Price  -- fix with dynamic SQL later
		, r.NAVDate_Price AS NAVDate_Price  -- fix with dynamic SQL later
		, r.Amort_Cost AS Sum_Of_Amort_Cost
		, r.MktVal AS Sum_Of_Mkt_Val
		, r.MktVal_Pct AS Percent_of_MarketValue
		, r.Net_Unreal_App AS Net_Unrealized_Appreciation
		, r.Pct_Net_Unreal_Gain AS Pct_Net_Unreal_Gain
		, r.DailyVal_Change AS Sum_Of_Daily_Value_Change
		, r.PriceChange_Pct AS Price_Chg_Pct 
		, r.Sum_Unreal_Dep AS Sum_Of_Unreal_Dep
		, r.Sum_Unreal_App AS Sum_Of_Unreal_App
		, r.Prop_Reason AS Proprietary_Reason
		, r.PricingMethod AS Pricing_Method_Type
		, r.TotalInt_Purch_Price AS Interest_purchase_price_method
		, r.TotalInt_Cur_Price AS Interest_current_price_method
		, r.Sum_OrigCost AS Sum_Of_Original_Cost
		, r.Security_Type
		--, r.FocusGroup
	FROM
		@results r
	--WHERE
	--	r.FocusGroup = CASE WHEN @ReportName = 'ValComm_Summary_Focus' THEN 'Yes' ELSE r.FocusGroup END
	
END

IF(@ReportName = 'GSO')
BEGIN
	SELECT
		r.PortfolioID
		, r.ExternalID
		, r.Issuer_Name
		, r.Sum_Par
		, r.Weight_Price
		, r.NAVDate_Price
		, r.NotionalVal
		, r.GSO_Industry
		, r.SettleDate_WeightedAvg
		, r.MaturityDate
		, r.Spread
		, r.[Floor]
		, r.FixedRate
		, r.InstrClass
		, r.IsQualified
		, r.PricingMethod
		, r.TotalInt_Purch_Price
		, r.TotalInt_Cur_Price
		, r.MktVal
		, r.BaseRateType
		, r.Amort_Cost
	FROM
		@results r
	ORDER BY
		r.Port_SortOrder asc
		, r.Inst_SortOrder asc

END	
		
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'Live', 'PROCEDURE', N'DailyReporting', NULL, NULL
GO
